這裡有一個要做 unpivot 的
https://ithelp.ithome.com.tw/questions/10197236

在Postgresql 中,並沒有直接做 pivot / unpivot 的,有一個 extension
叫做tablefun, 裡面有提供 crosstab(). 也有不少範例可以查找到.

在這個發問中,也有網友提出了 case when 之類的解法.
今天我們來利用 Postgresql 的 hstore 這個 extension, 來把各欄位與其值,一次性產生一個
hstore, 並利用 each() 來展開,這樣就有 unpivot 的功能了.
這部分先建立一個 view,方便後續處理.
接著看到是要再分三欄,這裡我們將用 join subquery 的方式來產生,但是這時候會有排序
未依照原本順序的情況,因為each()會依照 key 的長度遞增來產生.
所以我們建立一個輔助表,來指定排序順序.
接著來看 code吧.

create table it200218 (
  id int not null primary key
, ymid int not null
, e_year int not null
, e_month int not null
, e_eng int not null
, e_ice int not null
, e_u int not null
, e_baking int not null
, e_food int not null
, e_res int not null
, e_q int not null
, e_adm int not null
, e_711 int not null
, e_sum int not null
);

insert into it200218 values
(6, 20191, 2019, 1, 41686, 345879, 214507, 436234, 320875, 28158, 14126, 5651, 15053, 1422169),
(7, 20192, 2019, 2, 12321, 12313, 12312123, 11122, 222, 55555, 1111, 22222, 22222, 12449211),
(8, 20193, 2019, 3, 11111, 22222, 3333, 4444, 5555, 66666, 7777, 8888, 99999, 229995);

create view v_it200218
as
select id
     , (hs).key
     , (hs).value as val
  from (select id
             , each(hstore(bar)) as hs
          from it200218 as bar) a;
--
select key, val
  from v_it200218 
 where id = 6;
+----------+---------+
|   key    |   val   |
+----------+---------+
| id       | 6       |
| e_q      | 14126   |
| e_u      | 214507  |
| ymid     | 20191   |
| e_711    | 15053   |
| e_adm    | 5651    |
| e_eng    | 41686   |
| e_ice    | 345879  |
| e_res    | 28158   |
| e_sum    | 1422169 |
| e_food   | 320875  |
| e_year   | 2019    |
| e_month  | 1       |
| e_baking | 436234  |
+----------+---------+
(14 rows)

-- 建立 輔助表 來指定排序順序

create table it200218_aux (
  id int generated always as identity primary key
, name text
);

insert into it200218_aux(name) values
('id'), ('ymid'), ('e_year'),
('e_month'), ('e_eng'), ('e_ice'), ('e_u'), ('e_baking'),
('e_food'), ('e_res'), ('e_q'), ('e_adm'), ('e_711'), ('e_sum');


select a.key
     , a.val as col1
     , b.val as col2
     , c.val as col3
  from (select key, val
          from v_it200218 
         where id = 6) a
  join (select key, val
          from v_it200218 
         where id = 7) b
    on (a.key = b.key)
  join (select key, val
          from v_it200218 
         where id = 8) c
    on (b.key = c.key)
  join it200218_aux d
    on (c.key = d.name)
 order by d.id;
 
+----------+---------+----------+--------+
|   key    |  col1   |   col2   |  col3  |
+----------+---------+----------+--------+
| id       | 6       | 7        | 8      |
| ymid     | 20191   | 20192    | 20193  |
| e_year   | 2019    | 2019     | 2019   |
| e_month  | 1       | 2        | 3      |
| e_eng    | 41686   | 12321    | 11111  |
| e_ice    | 345879  | 12313    | 22222  |
| e_u      | 214507  | 12312123 | 3333   |
| e_baking | 436234  | 11122    | 4444   |
| e_food   | 320875  | 222      | 5555   |
| e_res    | 28158   | 55555    | 66666  |
| e_q      | 14126   | 1111     | 7777   |
| e_adm    | 5651    | 22222    | 8888   |
| e_711    | 15053   | 22222    | 99999  |
| e_sum    | 1422169 | 12449211 | 229995 |
+----------+---------+----------+--------+
(14 rows)

-- 使用輔助表的方式,我們可以靈活的依照想要的順序建立,
-- 但若只是想要依照原本建立的順序,又不想打許多字時,
-- 可以透過 pg_catalog 來做 join.
-- 當然建立輔助表時,也可以透過 pg_catalog 來取出欄位名稱.
-- 可以參考之前的 https://ithelp.ithome.com.tw/articles/10230070
-- 接著來看使用 pg_catalog 的方法.

select a.key
     , a.val as col1
     , b.val as col2
     , c.val as col3
  from (select key, val
          from v_it200218 
         where id = 6) a
  join (select key, val
          from v_it200218 
         where id = 7) b
    on (a.key = b.key)
  join (select key, val
          from v_it200218 
         where id = 8) c
    on (b.key = c.key)
  join (select attnum
             , attname
          from pg_attribute x
          join pg_class y
            on y.oid = x.attrelid
           and y.relname = 'it200218'
           and y.relkind = 'r'
           and x.attnum > 0
           and x.attisdropped = False) d
     on (c.key = d.attname)
  order by d.attnum;
 
關於hstore 之前我有介紹過
https://ithelp.ithome.com.tw/articles/10227108

https://ithelp.ithome.com.tw/articles/10227325